﻿using Com.JunCaiSoft.ReportCenter.Model;
using Com.JunCaiSoft.Common.IDao;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Com.JunCaiSoft.ConstructionCashFlow.IDao;
using Com.JunCaiSoft.ConstructionCashFlow.Entity;
using Com.JunCaiSoft.AutoMapper;
using System.Data;
using Com.JunCaiSoft.Common.AssistClass;
using Com.JunCaiSoft.ConstructionCashFlow.Common.Enum;
using System.Text.RegularExpressions;
using Com.JunCaiSoft.Common.Extension;
using System.Text;
using System.Net;
using System.IO;
using Microsoft.Extensions.Configuration;
using IConfiguration = Microsoft.Extensions.Configuration.IConfiguration;
using Microsoft.AspNetCore.Hosting;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HSSF.Util;

namespace Com.JunCaiSoft.API.ReportCenter.Controllers
{
	[Route("api/[controller]/[action]")]
	[ApiController]
	[Authorize]
	public class FundsReportController : BaseReportController<BankSettlement>
    {
		private IBankSettlementDao BankSettlementDao { get; set; }
		private IProjectSettlementDao ProjectSettlementDao { get; set; }
		private IConfiguration Configuration { get; set; }

		public FundsReportController(IBankSettlementDao banksettlementdao,IProjectSettlementDao projectsettlementdao, IConfiguration configuration, IWebHostEnvironment env) : base(banksettlementdao)
        {
			BankSettlementDao = banksettlementdao;
			ProjectSettlementDao = projectsettlementdao;

			var builder = new ConfigurationBuilder()
							   .SetBasePath(env.ContentRootPath)
							   .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
							   .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true, reloadOnChange: true)
							   .AddJsonFile("DataBaseExcute.json", optional: true, reloadOnChange: true);

			Configuration = builder.Build();
		}
        /// <summary>
        /// 按银行口径查询
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="keyword"></param>
        /// <param name="processingtype"></param>
        /// <param name="queryway"></param>
        /// <param name="periodtype"></param>
        /// <returns></returns>
        [HttpGet]
        public virtual async Task<ActionResult> QueryReportByBank(int pageIndex,int pageSize,String keyword,Int32 processingtype, Int32 queryway, SettlementPeriod periodtype)
        {
            try
            {

				if(pageSize == 0)
                {
					pageSize = 15;
                }

                String dateRange = Request.Query["dateRange[]"];

                var dateRangeList = dateRange.Split(',');

                DateTime beginTime = DateTime.Parse(dateRangeList[0]);

                DateTime endTime = DateTime.Parse(dateRangeList[1]);

				DateTimeExtension.dt = beginTime;

				DateTime lastBeginTime = beginTime.AddDays(-2);

				DateTime lastEndTime = beginTime.AddDays(-1);

				switch (periodtype)
                {
					case SettlementPeriod.BEGIN:
						break;
					case SettlementPeriod.DAY:
						await BankSettlementDao.SettlementBankAsync(
							lastBeginTime,
							lastEndTime,
							processingtype,
							periodtype);
						await ProjectSettlementDao.SettlementProjectAsync(
							lastBeginTime,
							lastEndTime,
							processingtype,
							periodtype);
						break;
					case SettlementPeriod.WEEK:
						lastBeginTime = DateTime.Parse(DateTimeExtension.GetBeforeWeekFirstDay());
						lastEndTime = DateTime.Parse(DateTimeExtension.GetBeforeWeekLastDay());
						await BankSettlementDao.SettlementBankAsync(
							lastBeginTime,
							lastEndTime, 
							processingtype,
							periodtype);
						await ProjectSettlementDao.SettlementProjectAsync(
							lastBeginTime,
							lastEndTime,
							processingtype,
							periodtype);
						break;
					case SettlementPeriod.MONTH:
						lastBeginTime = DateTime.Parse(DateTimeExtension.GetBeforeMonthFirstDay());
						lastEndTime = DateTime.Parse(DateTimeExtension.GetBeforeMonthLastDay());
						await BankSettlementDao.SettlementBankAsync(
							lastBeginTime,
							lastEndTime,
							processingtype,
							periodtype);
						await ProjectSettlementDao.SettlementProjectAsync(
							lastBeginTime,
							lastEndTime,
							processingtype,
							periodtype);
						break;
					case SettlementPeriod.YEAR:
						lastBeginTime = DateTime.Parse(DateTimeExtension.GetBeforeYearFirstDay());
						lastEndTime = DateTime.Parse(DateTimeExtension.GetBeforeYearLastDay());
						await BankSettlementDao.SettlementBankAsync(
							lastBeginTime,
							lastEndTime,
							processingtype,
							periodtype);
						await ProjectSettlementDao.SettlementProjectAsync(
							lastBeginTime,
							lastEndTime,
							processingtype,
							periodtype);
						break;
					default:

						await BankSettlementDao.SettlementBankAsync(
							lastBeginTime,
							lastEndTime,
							processingtype,
							periodtype);
						await ProjectSettlementDao.SettlementProjectAsync(
							lastBeginTime,
							lastEndTime,
							processingtype,
							periodtype);
						break;
				}

				List<SqlParameter> bankParams = new List<SqlParameter>() { };

				var bankTables = @" (   SELECT
											a.Id,
											a.Num,
											a.Name BankName,
											SUBSTRING ( a.Account, LEN( a.Account ) - 3, 4 ) AccountTail,
											ROUND( AVG ( b.EndBalance ), 2 ) BeginBalance,
											ROUND( AVG ( b.EndCommercialTicket ), 2 ) BeginCommercialTicket,
											ROUND( AVG ( b.EndFreezeMoney ), 2 ) BeginFreezeMoney,
											ROUND( SUM ( CASE WHEN c.BankForward = 0 THEN c.Amount ELSE 0.0 END ), 2 ) Income,
											ROUND( SUM ( CASE WHEN c.BankForward = 0 AND d.Name = '内部往来' THEN c.Amount ELSE 0.0 END ), 2 ) IncomeInnerTransaction,
											ROUND( SUM ( CASE WHEN c.BankForward = 0 AND d.Name = '房产往来' THEN c.Amount ELSE 0.0 END ), 2 ) IncomeRealEstateTransaction,
											ROUND( SUM ( CASE WHEN c.BankForward = 0 AND d.Name = '商票' THEN c.Amount ELSE 0.0 END ), 2 ) IncomeCommercialTicket,
											ROUND( SUM ( CASE WHEN c.BankForward = 0 AND d.Name = '垫资款' THEN c.Amount ELSE 0.0 END ), 2 ) IncomeAdvanceFund,
											ROUND( SUM ( CASE WHEN c.BankForward = 0 AND d.Name = '保证金' THEN c.Amount ELSE 0.0 END ), 2 ) IncomeSecurity,
											ROUND( SUM ( CASE WHEN c.BankForward = 0 AND d.Name = '银行借款' THEN c.Amount ELSE 0.0 END ), 2 ) IncomeBankLoan,
											ROUND( SUM ( CASE WHEN c.BankForward = 0 AND d.Name = '工程款' THEN c.Amount ELSE 0.0 END ), 2 ) IncomeProject,
											ROUND( SUM ( CASE WHEN c.BankForward = 0 AND d.Name = '管理费' THEN c.Amount ELSE 0.0 END ), 2 ) IncomeManage,
											ROUND( SUM ( CASE WHEN c.BankForward = 0 AND d.Name = '税金' THEN c.Amount ELSE 0.0 END ), 2 ) IncomeTax,
											ROUND( SUM ( CASE WHEN c.BankForward = 0 AND d.Name = '改造费' THEN c.Amount ELSE 0.0 END ), 2 ) IncomeRebuild,
											ROUND( SUM ( CASE WHEN c.BankForward = 0 AND d.Name = '材料款' THEN c.Amount ELSE 0.0 END ), 2 ) IncomeMaterial,
											ROUND( SUM ( CASE WHEN c.BankForward = 0 AND d.Name = '人工费' THEN c.Amount ELSE 0.0 END ), 2 ) IncomeLabor,
											ROUND( SUM ( CASE WHEN c.BankForward = 0 AND d.Name = '报销款' THEN c.Amount ELSE 0.0 END ), 2 ) IncomeReimbursement,
											ROUND( SUM ( CASE WHEN c.BankForward = 0 AND d.Name = '冻结' THEN c.Amount ELSE 0.0 END ), 2 ) IncreaseFreezeMoney,
											ROUND( SUM ( CASE WHEN c.BankForward = 0 AND d.Name = '利息' OR d.Name = '贷款利息' THEN c.Amount ELSE 0.0 END ), 2 ) IncomeInterest,

											ROUND( SUM ( CASE WHEN c.BankForward = 1 THEN c.Amount ELSE 0.0 END ), 2 ) Cost,
											ROUND( SUM ( CASE WHEN c.BankForward = 1 AND d.Name = '内部往来' THEN c.Amount ELSE 0.0 END ), 2 ) CostInnerTransaction,
											ROUND( SUM ( CASE WHEN c.BankForward = 1 AND d.Name = '房产往来' THEN c.Amount ELSE 0.0 END ), 2 ) CostRealEstateTransaction,
											ROUND( SUM ( CASE WHEN c.BankForward = 1 AND d.Name = '商票' THEN c.Amount ELSE 0.0 END ), 2 ) CostCommercialTicket,
											ROUND( SUM ( CASE WHEN c.BankForward = 1 AND d.Name = '垫资款' THEN c.Amount ELSE 0.0 END ), 2 ) CostAdvanceFund,
											ROUND( SUM ( CASE WHEN c.BankForward = 1 AND d.Name = '保证金' THEN c.Amount ELSE 0.0 END ), 2 ) CostSecurity,
											ROUND( SUM ( CASE WHEN c.BankForward = 1 AND d.Name = '银行借款' THEN c.Amount ELSE 0.0 END ), 2 ) CostBankLoan,
											ROUND( SUM ( CASE WHEN c.BankForward = 1 AND d.Name = '工程款' THEN c.Amount ELSE 0.0 END ), 2 ) CostProject,
											ROUND( SUM ( CASE WHEN c.BankForward = 1 AND d.Name = '管理费' THEN c.Amount ELSE 0.0 END ), 2 ) CostManage,
											ROUND( SUM ( CASE WHEN c.BankForward = 1 AND d.Name = '税金' THEN c.Amount ELSE 0.0 END ), 2 ) CostTax,
											ROUND( SUM ( CASE WHEN c.BankForward = 1 AND d.Name = '改造费' THEN c.Amount ELSE 0.0 END ), 2 ) CostRebuild,
											ROUND( SUM ( CASE WHEN c.BankForward = 1 AND d.Name = '材料款' THEN c.Amount ELSE 0.0 END ), 2 ) CostMaterial,
											ROUND( SUM ( CASE WHEN c.BankForward = 1 AND d.Name = '人工费' THEN c.Amount ELSE 0.0 END ), 2 ) CostLabor,
											ROUND( SUM ( CASE WHEN c.BankForward = 1 AND d.Name = '报销款' THEN c.Amount ELSE 0.0 END ), 2 ) CostReimbursement,
											ROUND( SUM ( CASE WHEN c.BankForward = 1 AND d.Name = '冻结' THEN c.Amount ELSE 0.0 END ), 2 ) ReduceFreezeMoney,
											ROUND( SUM ( CASE WHEN c.BankForward = 1 AND d.Name = '利息' OR d.Name = '贷款利息' THEN c.Amount ELSE 0.0 END ), 2 ) CostInterest
										FROM
											Bank a
											LEFT JOIN (
												SELECT
													* 
												FROM
													( 
														SELECT 
															*, ROW_NUMBER () OVER ( PARTITION BY SettlementBankId ORDER BY CreateTime DESC ) rn 
														FROM 
															BankSettlement a 
														WHERE 
															a.SettlementPeriod = @SettlementPeriod
															AND a.BeginTime >= CAST (  @LastBeginTime AS datetime2 ( 7 ) ) 
															AND a.EndTime <= CAST (  @LastEndTime AS datetime2 ( 7 ) ) 
													) t 
												WHERE
													rn = 1 
											) b ON a.Id = b.SettlementBankId ";

				var joinType = (queryway == 1 ? " INNER " : " LEFT ") + " JOIN ( SELECT * FROM CashFlow WHERE AllocationTime >= @BeginTime AND AllocationTime <= @EndTime ) c ON a.Id = c.BankId ";

				bankTables += joinType;

				var joinFundsType = " LEFT JOIN FundsType d on c.FundsType = d.Code";

				bankTables += joinFundsType;

				var bankWhere = @" ";

				if(keyword != "" && keyword != null)
                {
					Regex rex = new Regex(@"^\d{4}$");

					if (keyword.Length == 4 && rex.IsMatch(keyword))
                    {
						bankWhere += " WHERE a.Account like @BankNameOrBankTail ";

						SqlParameter keywordParam = new SqlParameter("@BankNameOrBankTail", "%" + keyword);

						bankParams.Add(keywordParam);
					}
                    else
                    {
						bankWhere += " WHERE a.Name like @BankNameOrBankTail ";

						SqlParameter keywordParam = new SqlParameter("@BankNameOrBankTail", "%" + keyword + "%");

						bankParams.Add(keywordParam);
					}
						
				}
				
				var bankGroupBy = @" GROUP BY
										a.Id,
										a.Num,
										a.Name,
										SUBSTRING ( a.Account, Len( a.Account ) - 3, 4 ) ) a ";

				var excuteRawSQL = bankTables + bankWhere + bankGroupBy;

				

				SqlParameter BeginTime = new SqlParameter("@BeginTime", beginTime.ToString("yyyyMMdd"));

				SqlParameter EndTime = new SqlParameter("@EndTime", endTime.ToString("yyyyMMdd"));

				SqlParameter LastBeginTime = new SqlParameter("@LastBeginTime", lastBeginTime.ToString("yyyyMMdd"));

				SqlParameter LastEndTime = new SqlParameter("@LastEndTime", lastEndTime.ToString("yyyyMMdd"));

				SqlParameter SettlementPeriodParam = new SqlParameter("@SettlementPeriod", periodtype);

				bankParams.Add(BeginTime); bankParams.Add(EndTime);bankParams.Add(SettlementPeriodParam);bankParams.Add(LastBeginTime); bankParams.Add(LastEndTime);

				//var where = @" a.Id = b.SettlementBankId ";

				var bankList = await DaoAsync.QuerySQLByPageQuickAsync<BankItem>(pageIndex, pageSize, " a.BankName asc ", null, excuteRawSQL, null, bankParams.Select(x => ((ICloneable)x).Clone()).ToArray());

                var bankIdList = "";

                if (bankList != null && bankList.items != null)
                {
                    foreach (var item in bankList.items)
                    {
                        bankIdList +=  "'" + item.Id + "',";
                    }
                }

                bankIdList = bankIdList.Length != 0 ? bankIdList.Substring(0, bankIdList.Length - 1) : "";

				List<SqlParameter> projectParams = new List<SqlParameter>() { };

				projectParams.Add(BeginTime); projectParams.Add(EndTime); projectParams.Add(LastBeginTime); projectParams.Add(LastEndTime);projectParams.Add(SettlementPeriodParam);

				var projectSQL = @" SELECT
										a.ProjectId ProjectId,
										a.BankId BankId,
										'' AccountTail,
										b.Name BankName,
										ROUND( AVG ( c.EndBalance ), 2 ) BeginBalance,
										ROUND( AVG ( c.EndCommercialTicket ), 2 ) BeginCommercialTicket,
										ROUND( AVG ( c.EndFreezeMoney ), 2 ) BeginFreezeMoney,
										ROUND( SUM ( CASE WHEN a.BankForward = 0 AND d.Name != '冻结' THEN a.Amount ELSE 0.0 END ), 2 ) Income,
										ROUND( SUM ( CASE WHEN a.BankForward = 0 AND d.Name = '内部往来' THEN a.Amount ELSE 0.0 END ), 2 ) IncomeInnerTransaction,
										ROUND( SUM ( CASE WHEN a.BankForward = 0 AND d.Name = '房产往来' THEN a.Amount ELSE 0.0 END ), 2 ) IncomeRealEstateTransaction,
										ROUND( SUM ( CASE WHEN a.BankForward = 0 AND d.Name = '商票' THEN a.Amount ELSE 0.0 END ), 2 ) IncomeCommercialTicket,
										ROUND( SUM ( CASE WHEN a.BankForward = 0 AND d.Name = '垫资款' THEN a.Amount ELSE 0.0 END ), 2 ) IncomeAdvanceFund,
										ROUND( SUM ( CASE WHEN a.BankForward = 0 AND d.Name = '保证金' THEN a.Amount ELSE 0.0 END ), 2 ) IncomeSecurity,
										ROUND( SUM ( CASE WHEN a.BankForward = 0 AND d.Name = '银行借款' THEN a.Amount ELSE 0.0 END ), 2 ) IncomeBankLoan,
										ROUND( SUM ( CASE WHEN a.BankForward = 0 AND d.Name = '工程款' THEN a.Amount ELSE 0.0 END ), 2 ) IncomeProject,
										ROUND( SUM ( CASE WHEN a.BankForward = 0 AND d.Name = '管理费' THEN a.Amount ELSE 0.0 END ), 2 ) IncomeManage,
										ROUND( SUM ( CASE WHEN a.BankForward = 0 AND d.Name = '税金' THEN a.Amount ELSE 0.0 END ), 2 ) IncomeTax,
										ROUND( SUM ( CASE WHEN a.BankForward = 0 AND d.Name = '改造费' THEN a.Amount ELSE 0.0 END ), 2 ) IncomeRebuild,
										ROUND( SUM ( CASE WHEN a.BankForward = 0 AND d.Name = '材料款' THEN a.Amount ELSE 0.0 END ), 2 ) IncomeMaterial,
										ROUND( SUM ( CASE WHEN a.BankForward = 0 AND d.Name = '人工费' THEN a.Amount ELSE 0.0 END ), 2 ) IncomeLabor,
										ROUND( SUM ( CASE WHEN a.BankForward = 0 AND d.Name = '报销款' THEN a.Amount ELSE 0.0 END ), 2 ) IncomeReimbursement,
										ROUND( SUM ( CASE WHEN a.BankForward = 0 AND d.Name = '冻结' THEN a.Amount ELSE 0.0 END ), 2 ) IncreaseFreezeMoney,
										ROUND( SUM ( CASE WHEN a.BankForward = 0 AND d.Name = '利息' OR d.Name = '贷款利息' THEN a.Amount ELSE 0.0 END ), 2 ) IncomeInterest,
															   					 
										ROUND( SUM ( CASE WHEN a.BankForward = 1 AND d.Name != '冻结' THEN a.Amount ELSE 0.0 END ), 2 ) Cost,
										ROUND( SUM ( CASE WHEN a.BankForward = 1 AND d.Name = '内部往来' THEN a.Amount ELSE 0.0 END ), 2 ) CostInnerTransaction,
										ROUND( SUM ( CASE WHEN a.BankForward = 1 AND d.Name = '房产往来' THEN a.Amount ELSE 0.0 END ), 2 ) CostRealEstateTransaction,
										ROUND( SUM ( CASE WHEN a.BankForward = 1 AND d.Name = '商票' THEN a.Amount ELSE 0.0 END ), 2 ) CostCommercialTicket,
										ROUND( SUM ( CASE WHEN a.BankForward = 1 AND d.Name = '垫资款' THEN a.Amount ELSE 0.0 END ), 2 ) CostAdvanceFund,
										ROUND( SUM ( CASE WHEN a.BankForward = 1 AND d.Name = '保证金' THEN a.Amount ELSE 0.0 END ), 2 ) CostSecurity,
										ROUND( SUM ( CASE WHEN a.BankForward = 1 AND d.Name = '银行借款' THEN a.Amount ELSE 0.0 END ), 2 ) CostBankLoan,
										ROUND( SUM ( CASE WHEN a.BankForward = 1 AND d.Name = '工程款' THEN a.Amount ELSE 0.0 END ), 2 ) CostProject,
										ROUND( SUM ( CASE WHEN a.BankForward = 1 AND d.Name = '管理费' THEN a.Amount ELSE 0.0 END ), 2 ) CostManage,
										ROUND( SUM ( CASE WHEN a.BankForward = 1 AND d.Name = '税金' THEN a.Amount ELSE 0.0 END ), 2 ) CostTax,
										ROUND( SUM ( CASE WHEN a.BankForward = 1 AND d.Name = '改造费' THEN a.Amount ELSE 0.0 END ), 2 ) CostRebuild,
										ROUND( SUM ( CASE WHEN a.BankForward = 1 AND d.Name = '材料款' THEN a.Amount ELSE 0.0 END ), 2 ) CostMaterial,
										ROUND( SUM ( CASE WHEN a.BankForward = 1 AND d.Name = '人工费' THEN a.Amount ELSE 0.0 END ), 2 ) CostLabor,
										ROUND( SUM ( CASE WHEN a.BankForward = 1 AND d.Name = '报销款' THEN a.Amount ELSE 0.0 END ), 2 ) CostReimbursement,
										ROUND( SUM ( CASE WHEN a.BankForward = 1 AND d.Name = '冻结' THEN a.Amount ELSE 0.0 END ), 2 ) ReduceFreezeMoney,
										ROUND( SUM ( CASE WHEN a.BankForward = 1 AND d.Name = '利息' OR d.Name = '贷款利息' THEN a.Amount ELSE 0.0 END ), 2 ) CostInterest
									FROM
										( SELECT * FROM CashFlow WHERE AllocationTime >= @BeginTime AND AllocationTime <= @EndTime ) a
										LEFT JOIN FundsType d ON a.FundsType = d.Code
										LEFT JOIN Project b ON a.ProjectId = b.Id
										LEFT JOIN (
											SELECT
												* 
											FROM
												( 
													SELECT 
														*, ROW_NUMBER () OVER ( PARTITION BY SettlementProjectId ORDER BY CreateTime DESC ) rn 
													FROM 
														ProjectSettlement a 
													WHERE 
														a.SettlementPeriod =  @SettlementPeriod
														AND a.BeginTime >= CAST (  @LastBeginTime AS datetime2 ( 7 ) ) 
														AND a.EndTime <= CAST (  @LastEndTime AS datetime2 ( 7 ) ) 
												) t 
											WHERE
												rn = 1 
										) c ON b.Id = c.SettlementProjectId ";

				if(bankIdList == "" || bankIdList == null)
                {
					bankIdList = "'00000000-0000-0000-0000-000000000000'";
				}

				var ProjectWhere = @"WHERE
										a.BankId in ( " + bankIdList + " ) ";

				var ProjectGroupBy = @"GROUP BY
										a.BankId,
										a.ProjectId,
										b.Name ";
				var ExcuteProjectSQL = projectSQL + ProjectWhere + ProjectGroupBy;

				//new SqlParameter[] { }

				var projectList = await DaoAsync.QuerySQLAsync<ProjectItem>(ExcuteProjectSQL, projectParams.Select(x => ((ICloneable)x).Clone()).ToArray());

				var result = new BankReport
				{
					totalCount = bankList.totalCount,
					list = bankList.items.Select(x => x.MapTo<BankReportItem>()).ToList()
				};

				result.list.ForEach(x => x.children = projectList.Where(y => x.Id == y.BankId).Select(x => x.MapTo<BankReportChildrenItem>()).ToList());

				return await Task.Run(() => Content(JsonConvert.SerializeObject(result)));
            }
            catch(Exception e)
            {
                throw e;
            }
        }
        /// <summary>
        /// 按项目口径查询
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="keyword"></param>
        /// <param name="processingtype"></param>
        /// <param name="queryway"></param>
        /// <param name="periodtype"></param>
        /// <returns></returns>
        [HttpGet]
		public virtual async Task<ActionResult> QueryReportByProject(int pageIndex, int pageSize, String keyword,Int32 processingtype, Int32 queryway, SettlementPeriod periodtype)
        {
            try
            {
				String dateRange = Request.Query["dateRange[]"];

				var dateRangeList = dateRange.Split(',');

				DateTime beginTime = DateTime.Parse(dateRangeList[0]);

				DateTime endTime = DateTime.Parse(dateRangeList[1]);

				DateTimeExtension.dt = beginTime;

				DateTime lastBeginTime = beginTime.AddDays(-2);

				DateTime lastEndTime = beginTime.AddDays(-1);

				switch (periodtype)
				{
					case SettlementPeriod.BEGIN:
						break;
					case SettlementPeriod.DAY:
						
						await BankSettlementDao.SettlementBankAsync(
							lastBeginTime,
							lastEndTime,
							processingtype,
							periodtype);
						await ProjectSettlementDao.SettlementProjectAsync(
							lastBeginTime,
							lastEndTime,
							processingtype,
							periodtype);
						break;
					case SettlementPeriod.WEEK:
						lastBeginTime = DateTime.Parse(DateTimeExtension.GetBeforeWeekFirstDay());
						lastEndTime = DateTime.Parse(DateTimeExtension.GetBeforeWeekLastDay());
						await BankSettlementDao.SettlementBankAsync(
							lastBeginTime,
							lastEndTime,
							processingtype,
							periodtype);
						await ProjectSettlementDao.SettlementProjectAsync(
							lastBeginTime,
							lastEndTime,
							processingtype,
							periodtype);
						break;
					case SettlementPeriod.MONTH:
						lastBeginTime = DateTime.Parse(DateTimeExtension.GetBeforeMonthFirstDay());
						lastEndTime = DateTime.Parse(DateTimeExtension.GetBeforeMonthLastDay());
						await BankSettlementDao.SettlementBankAsync(
							lastBeginTime,
							lastEndTime,
							processingtype,
							periodtype);
						await ProjectSettlementDao.SettlementProjectAsync(
							lastBeginTime,
							lastEndTime,
							processingtype,
							periodtype);
						break;
					case SettlementPeriod.YEAR:
						lastBeginTime = DateTime.Parse(DateTimeExtension.GetBeforeYearFirstDay());
						lastEndTime = DateTime.Parse(DateTimeExtension.GetBeforeYearLastDay());
						await BankSettlementDao.SettlementBankAsync(
							lastBeginTime,
							lastEndTime,
							processingtype,
							periodtype);
						await ProjectSettlementDao.SettlementProjectAsync(
							lastBeginTime,
							lastEndTime,
							processingtype,
							periodtype);
						break;
					default:

						await BankSettlementDao.SettlementBankAsync(
							lastBeginTime,
							lastEndTime,
							processingtype,
							periodtype);
						await ProjectSettlementDao.SettlementProjectAsync(
							lastBeginTime,
							lastEndTime,
							processingtype,
							periodtype);
						break;
				}

				var projectTables = @" ( SELECT
											a.Id,
											a.NumCode,
											a.Name ProjectName,
											ROUND( AVG ( b.BeginBalance ), 2 ) BeginBalance,
											ROUND( SUM ( CASE WHEN c.BankForward = 0 AND c.FundsType = 16 THEN c.Amount ELSE 0.0 END ), 2 ) IncomeCommercialTicket,
											ROUND( SUM ( CASE WHEN c.BankForward = 0 THEN c.Amount ELSE 0.0 END ), 2 ) Income,
											ROUND( SUM ( CASE WHEN c.BankForward = 1 AND c.FundsType = 16 THEN c.Amount ELSE 0.0 END ), 2 ) CostCommercialTicket,
											ROUND( SUM ( CASE WHEN c.BankForward = 1 THEN c.Amount ELSE 0.0 END ), 2 ) Cost,
											ROUND( AVG ( b.BeginFreezeMoney ), 2 ) BeginFreezeMoney,
											ROUND( AVG ( b.EndFreezeMoney ), 2 ) EndFreezeMoney,
											ROUND( AVG ( b.BeginCommercialTicket ), 2 ) BeginCommercialTicket,
											ROUND( AVG ( b.BeginGongDiFang ), 2 ) BeginGongDiFang,
											ROUND( AVG ( b.EndGongDiFang ), 2 ) EndGongDiFang 
										FROM
											Project a
											LEFT JOIN (
												SELECT
													* 
												FROM
													( 
														SELECT 
															*, ROW_NUMBER () OVER ( PARTITION BY SettlementProjectId ORDER BY CreateTime DESC ) rn 
														FROM 
															ProjectSettlement a 
														WHERE 
															a.SettlementPeriod = @SettlementPeriod
															AND a.BeginTime >= CAST (  @LastBeginTime AS datetime2 ( 7 ) ) 
															AND a.EndTime <= CAST (  @LastEndTime AS datetime2 ( 7 ) )  
													) t 
												WHERE
													rn = 1 
											) b ON a.Id = b.SettlementProjectId ";

				var joinType = (queryway == 1 ? " INNER " : " LEFT ") + " JOIN ( SELECT * FROM CashFlow WHERE AllocationTime >= @BeginTime AND AllocationTime <= @EndTime ) c ON a.Id = c.ProjectId ";

				projectTables += joinType;

				var bankWhere = @" ";

				if (keyword != "" && keyword != null)
				{
					bankWhere += " WHERE a.Name like @ProjectName ";
				}

				var projectGroupBy = @" GROUP BY
										a.Id,
										a.NumCode,
										a.Name ) a ";

				var excuteRawSQL = projectTables + bankWhere + projectGroupBy;

				List<SqlParameter> projectParams = new List<SqlParameter>() { };

				SqlParameter BeginTime = new SqlParameter("@BeginTime", beginTime.ToString("yyyyMMdd"));

				SqlParameter EndTime = new SqlParameter("@EndTime", endTime.ToString("yyyyMMdd"));

				SqlParameter LastBeginTime = new SqlParameter("@LastBeginTime", lastBeginTime.ToString("yyyyMMdd"));

				SqlParameter LastEndTime = new SqlParameter("@LastEndTime", lastEndTime.ToString("yyyyMMdd"));

				SqlParameter SettlementPeriodParam = new SqlParameter("@SettlementPeriod", periodtype);

				projectParams.Add(BeginTime); projectParams.Add(EndTime); projectParams.Add(SettlementPeriodParam); projectParams.Add(LastBeginTime); projectParams.Add(LastEndTime);

				if (keyword != "" && keyword != null)
				{
					SqlParameter keywordParam = new SqlParameter("@ProjectName", "%" + keyword + "%");

					projectParams.Add(keywordParam);
				}

				//var where = @" a.Id = b.SettlementProjectId ";

				var projectList = await DaoAsync.QuerySQLByPageQuickAsync<ProjectInfo>(pageIndex, pageSize, " a.ProjectName asc ", null, excuteRawSQL, null, projectParams.Select(x => ((ICloneable)x).Clone()).ToArray());

				var projectIdList = "";

				if (projectList != null && projectList.items != null)
				{
					foreach (var item in projectList.items)
					{
						projectIdList += "'" + item.Id + "',";
					}
				}

				projectIdList = projectIdList.Length != 0 ? projectIdList.Substring(0, projectIdList.Length - 1) : "";

				var projectSQL = @" SELECT
										a.ProjectId ProjectId,
										a.BankId BankId,
										b.Name ProjectName,
										ROUND( AVG ( c.BeginBalance ), 2 ) BeginBalance,
										ROUND( SUM ( CASE WHEN a.BankForward = 0 AND a.FundsType = 16 THEN a.Amount ELSE 0.0 END ), 2 ) IncomeCommercialTicket,
										ROUND( SUM ( CASE WHEN a.BankForward = 0 THEN a.Amount ELSE 0.0 END ), 2 ) Income,
										ROUND( SUM ( CASE WHEN a.BankForward = 1 AND a.FundsType = 16 THEN a.Amount ELSE 0.0 END ), 2 ) CostCommercialTicket,
										ROUND( SUM ( CASE WHEN a.BankForward = 1 THEN a.Amount ELSE 0.0 END ), 2 ) Cost,
										ROUND( AVG ( c.BeginFreezeMoney ), 2 ) BeginFreezeMoney,
										ROUND( AVG ( c.EndFreezeMoney ), 2 ) EndFreezeMoney,
										0 BeginCashBalance,
										0 EndCashBalance,
										0 BeginCommercialTicket,
										0 EndCommercialTicket,
										0 BeginGongDiFang,
										0 EndGongDiFang
									FROM
										( SELECT * FROM CashFlow WHERE AllocationTime >= @BeginTime AND AllocationTime <= @EndTime ) a
										LEFT JOIN Bank b ON a.BankId = b.Id 
										LEFT JOIN (
											SELECT
												* 
											FROM
												( 
													SELECT 
														*, ROW_NUMBER () OVER ( PARTITION BY SettlementBankId ORDER BY CreateTime DESC ) rn 
													FROM 
														BankSettlement a 
													WHERE 
														a.SettlementPeriod = @SettlementPeriod
														AND a.BeginTime >= CAST (  @LastBeginTime AS datetime2 ( 7 ) ) 
														AND a.EndTime <= CAST (  @LastEndTime AS datetime2 ( 7 ) ) 
												) t 
											WHERE
												rn = 1 
										) c ON b.Id = c.SettlementBankId
										";

				if (projectIdList == "" || projectIdList == null)
				{
					projectIdList = "'00000000-0000-0000-0000-000000000000'";
				}

				var ProjectWhere = @"WHERE
										a.ProjectId in ( " + projectIdList + " ) ";

				var ProjectGroupBy = @"GROUP BY
										a.BankId,
										a.ProjectId,
										b.Name ";
				var ExcuteProjectSQL = projectSQL + ProjectWhere + ProjectGroupBy;

				List<SqlParameter> bankParams = new List<SqlParameter>() { };

				bankParams.Add(BeginTime); bankParams.Add(EndTime); bankParams.Add(LastBeginTime); bankParams.Add(LastEndTime); bankParams.Add(SettlementPeriodParam);

				var bankList = await DaoAsync.QuerySQLAsync<BankInfo>(ExcuteProjectSQL, bankParams.Select(x => ((ICloneable)x).Clone()).ToArray());

				var result = new ProjectReport
				{
					totalCount = projectList.totalCount,
					list = projectList.items.Select(x => x.MapTo<ProjectReportItem>()).ToList()
				};

				result.list.ForEach(x => x.children = bankList.Where(y => x.Id == y.ProjectId).Select(x => x.MapTo<ProjectReportChildrenItem>()).ToList());

				return await Task.Run(() => Content(JsonConvert.SerializeObject(result)));
			}
			catch(Exception e)
            {
				throw e;
            }
        }
		/// <summary>
		/// 获取本周日期范围
		/// </summary>
		/// <returns></returns>
		[HttpGet]
		public virtual async Task<ActionResult> GetThisWeekRange()
        {
            try
            {
				var WeekRange = new List<String>()
				{
					DateTimeExtension.GetCurrentWeekFirstDay(),
					DateTimeExtension.GetCurrentWeekLastDay()
				};
				return await Task.Run(() => Content(JsonConvert.SerializeObject(WeekRange)));
			}
			catch(Exception e)
            {
				throw e;
            }
			
		}
		[HttpGet]
		public virtual async Task<ActionResult> GetThisMonthRange()
        {
			try
			{
				var MonthRange = new List<String>()
				{
					DateTimeExtension.GetCurrentMonthFirstDay(),
					DateTimeExtension.GetCurrentMonthLastDay()
				};
				return await Task.Run(() => Content(JsonConvert.SerializeObject(MonthRange)));
			}
			catch (Exception e)
			{
				throw e;
			}
		}
		[HttpGet]
		public virtual async Task<ActionResult> GetThisYearRange()
		{
			try
			{
				var YearRange = new List<String>()
				{
					DateTimeExtension.GetCurrentYearFirstDay(),
					DateTimeExtension.GetCurrentYearLastDay()
				};
				return await Task.Run(() => Content(JsonConvert.SerializeObject(YearRange)));
			}
			catch (Exception e)
			{
				throw e;
			}
		}
		[HttpGet]
		public virtual async Task<ActionResult> GetDatePickRange()
        {
			try
			{
				DateTimeExtension.dt = DateTime.Now;
				var DatePickRange = new List<Shortcuts>()
				{
					new Shortcuts(){  text = "按天",value = "() => ['" + DateTime.Now.ToString("yyyy-MM-dd") + "','" + DateTime.Now.ToString("yyyy-MM-dd") +  "']" },
					new Shortcuts(){  text = "按周",value = "() => ['" + DateTimeExtension.GetCurrentWeekFirstDay() + "','" + DateTimeExtension.GetCurrentWeekLastDay() +  "']" },
					new Shortcuts(){  text = "按月",value = "() => ['" + DateTimeExtension.GetCurrentMonthFirstDay() + "','" + DateTimeExtension.GetCurrentMonthLastDay() +  "']" },
					new Shortcuts(){  text = "按年",value = "() => ['" + DateTimeExtension.GetCurrentYearFirstDay() + "','" + DateTimeExtension.GetCurrentYearLastDay() +  "']" }
				};
				return await Task.Run(() => Content(JsonConvert.SerializeObject(DatePickRange)));
			}
			catch (Exception e)
			{
				throw e;
			}
		}
        [HttpGet]
        [AllowAnonymous]
        public virtual FileResult ExportData(String AccessToken, String BankOrProject,String periodtype)
        {
            var baseurl = Configuration.GetSection("APICores:BaseURL");

            var querystr = Request.QueryString;

			var periodtypeStr = "";

            switch (periodtype)
            {
				case "1":
					periodtypeStr = "日";
					break;
				case "2":
					periodtypeStr = "周";
					break;
				case "3":
					periodtypeStr = "月";
					break;
				case "4":
					periodtypeStr = "年";
					break;
				case "5":
					periodtypeStr = "自定义";
					break;
				default :
					periodtypeStr = "";
					break;
            }

            if (BankOrProject == "Bank")
            {
                var BankReportStr =
                    HttpExtension.HttpGet(baseurl.Value + "/FundsReport/QueryReportByBank?" + querystr, true, "application/json;",AccessToken);

				BankReport bankReport = JsonConvert.DeserializeObject<BankReport>(BankReportStr);

				var bankworkbook = new XSSFWorkbook();

				var banksheet = bankworkbook.CreateSheet("银行资金情况" + periodtypeStr + "报表（按银行口径）");

				var projectItem = new List<BankReportChildrenItem>();

				#region 银行数据处理

				#region 创建Excel表头
				banksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 35));

				IRow rowTitle = banksheet.CreateRow(0);//第一行选中

				rowTitle.Height = 30 * 20;//设置行高

				var CellTitle = rowTitle.CreateCell(0);

				//给标题头添加格式
				ICellStyle mainTitle = bankworkbook.CreateCellStyle();
				mainTitle.Alignment = HorizontalAlignment.Center;//单元格水平居中
				mainTitle.VerticalAlignment = VerticalAlignment.Center;//单元格垂直居中
				IFont mainFontTitle = bankworkbook.CreateFont();//新建一个字体样式对象
				mainFontTitle.Boldweight = short.MaxValue;//设置字体加粗样式
				mainFontTitle.FontHeightInPoints = 20;//设置字体大小
				mainTitle.SetFont(mainFontTitle);//使用SetFont方法将字体样式添加到单元格样式中
				CellTitle.CellStyle = mainTitle;

				CellTitle.SetCellValue("财务资金中心银行收支" + periodtypeStr + "报表");

				//表头明细开始
				ICellStyle headTitle = bankworkbook.CreateCellStyle();
				headTitle.Alignment = HorizontalAlignment.Center;//单元格水平居中
				headTitle.VerticalAlignment = VerticalAlignment.Center;//单元格垂直居中
				headTitle.FillForegroundColor = HSSFColor.Grey25Percent.Index;  //short.Parse("#E8EAEC");
				IFont headFontTitle = bankworkbook.CreateFont();//新建一个字体样式对象
				headFontTitle.Boldweight = short.MaxValue;//设置字体加粗样式
				headFontTitle.FontHeightInPoints = 12;//设置字体大小
				headTitle.SetFont(headFontTitle);

				IRow HeadRowTitle = banksheet.CreateRow(1);//第二行选中

				#region 第一行，第一列
				banksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 3, 0, 0));

				var OrderTitle = HeadRowTitle.CreateCell(0);

				OrderTitle.CellStyle = headTitle;

				OrderTitle.SetCellValue("序号");
				#endregion

				#region 第一行，第二列
				banksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 3, 1, 1));

				var AccountTailTitle = HeadRowTitle.CreateCell(1);

				AccountTailTitle.CellStyle = headTitle;

				AccountTailTitle.SetCellValue("账户尾号");
				#endregion

				#region 第一行，第三列
				banksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 3, 2, 2));

				var BankTitle = HeadRowTitle.CreateCell(2);

				BankTitle.CellStyle = headTitle;

				BankTitle.SetCellValue("银行名称");
				#endregion

				#region 第一行，第四列
				banksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 3, 7));

				var BeginTitle = HeadRowTitle.CreateCell(3);

				BeginTitle.CellStyle = headTitle;

				BeginTitle.SetCellValue("期初数");
				#endregion

				#region 第一行，第五列
				banksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 8, 17));

				var IncomeTitle = HeadRowTitle.CreateCell(8);

				IncomeTitle.CellStyle = headTitle;

				IncomeTitle.SetCellValue("本期收入");
				#endregion

				#region 第一行，第六列
				banksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 18, 30));

				var CostTitle = HeadRowTitle.CreateCell(18);

				CostTitle.CellStyle = headTitle;

				CostTitle.SetCellValue("本期支出");
				#endregion

				#region 第一行，第七列
				banksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 31, 35));

				var EndTitle = HeadRowTitle.CreateCell(31);

				EndTitle.CellStyle = headTitle;

				EndTitle.SetCellValue("期末数");
				#endregion

				IRow BalanceRowTitle = banksheet.CreateRow(2);//第三行选中

				#region 第二行，第一列
				banksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 3, 5));

				var BeginBalanceTitle = BalanceRowTitle.CreateCell(3);

				BeginBalanceTitle.CellStyle = headTitle;

				BeginBalanceTitle.SetCellValue("期初余额");
				#endregion

				#region 第二行，第二列
				banksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 31, 33));

				var EndBalanceTitle = BalanceRowTitle.CreateCell(31);

				EndBalanceTitle.CellStyle = headTitle;

				EndBalanceTitle.SetCellValue("期末余额");
				#endregion

				#region 第二行，倒数第二列
				banksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 3, 34, 34));

				var EndFreezeMoney = BalanceRowTitle.CreateCell(34);

				EndFreezeMoney.CellStyle = headTitle;

				EndFreezeMoney.SetCellValue("冻结资金");
				#endregion

				#region 第二行，倒数第一列
				banksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 3, 35, 35));

				var EndBalance = BalanceRowTitle.CreateCell(35);

				EndBalance.CellStyle = headTitle;

				EndBalance.SetCellValue("可用余额");
				#endregion

				#region 第二行各列
				String[] itemArr = new String[]
				{
					"冻结资金","可用余额",
					"内部往来","房产往来","垫资款","保证金","银行借款","工程款", "管理费","税金","其他","收入合计",
					"内部往来","房产往来","垫资款","保证金","银行借款","利息","材料款","人工费","管理费","税金","报销款","其他","支出合计"
				};

				for (int i = 6; i < 31; i++)
				{
					banksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 3, i, i));

					var ItemTitle = BalanceRowTitle.CreateCell(i);

					ItemTitle.CellStyle = headTitle;

					ItemTitle.SetCellValue(itemArr[i - 6]);
				}
				#endregion

				IRow BalanceDetailRowTitle = banksheet.CreateRow(3);//第四行选中

				#region 第四行填充
				String[] balanceItem = new String[] { "现金", "商票", "小计" };

				for (int i = 3; i < 6; i++)
				{
					var BeginItemTitle = BalanceDetailRowTitle.CreateCell(i);

					BeginItemTitle.CellStyle = headTitle;

					BeginItemTitle.SetCellValue(balanceItem[i - 3]);

					var EndItemTitle = BalanceDetailRowTitle.CreateCell(i + 28);

					EndItemTitle.CellStyle = headTitle;

					EndItemTitle.SetCellValue(balanceItem[i - 3]);
				}
				#endregion

				#endregion //表头结尾处

				#region 表体数据处理

				//表头明细开始
				ICellStyle bodystyle = bankworkbook.CreateCellStyle();
				bodystyle.Alignment = HorizontalAlignment.Left;//单元格水平居中
				bodystyle.VerticalAlignment = VerticalAlignment.Center;//单元格垂直居中
				bodystyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;  //short.Parse("#E8EAEC");
				IFont bodyFontTitle = bankworkbook.CreateFont();//新建一个字体样式对象
				bodyFontTitle.Boldweight = short.MaxValue;//设置字体加粗样式
				bodyFontTitle.FontHeightInPoints = 10;//设置字体大小
				bodystyle.SetFont(bodyFontTitle);

				int rowNum = 0;

				foreach (var item in bankReport.list)
				{
					var BankRowNum = rowNum + 4;

					IRow BankRow = banksheet.CreateRow(BankRowNum);//第rowNum行数据操作(第五行开始)

					#region Bank行赋值
					var propcell_0 = BankRow.CreateCell(0);

					bodystyle.Alignment = HorizontalAlignment.Center;

					propcell_0.CellStyle = bodystyle;

					propcell_0.SetCellValue((rowNum + 1).ToString());

					var propcell_1 = BankRow.CreateCell(1);

					propcell_1.CellStyle = bodystyle;

					propcell_1.SetCellValue(item.AccountTail);

					var propcell_2 = BankRow.CreateCell(2);

					propcell_2.CellStyle = bodystyle;

					propcell_2.SetCellValue(item.BankName);

					bodystyle.Alignment = HorizontalAlignment.Left;

					var propcell_3 = BankRow.CreateCell(3);

					propcell_3.CellStyle = bodystyle;

					propcell_3.SetCellValue(item.BeginCash);

					var propcell_4 = BankRow.CreateCell(4);

					propcell_4.CellStyle = bodystyle;

					propcell_4.SetCellValue(item.BeginCommercialTicket);

					var propcell_5 = BankRow.CreateCell(5);

					propcell_5.CellStyle = bodystyle;

					propcell_5.SetCellValue(item.BeginBalance);

					var propcell_6 = BankRow.CreateCell(6);

					propcell_6.CellStyle = bodystyle;

					propcell_6.SetCellValue(item.BeginFreezeMoney);

					var propcell_7 = BankRow.CreateCell(7);

					propcell_7.CellStyle = bodystyle;

					propcell_7.SetCellValue(item.BeginBalance);

					var propcell_8 = BankRow.CreateCell(8);

					propcell_8.CellStyle = bodystyle;

					propcell_8.SetCellValue(item.IncomeInnerTransaction);

					var propcell_9 = BankRow.CreateCell(9);

					propcell_9.CellStyle = bodystyle;

					propcell_9.SetCellValue(item.IncomeRealEstateTransaction);

					var propcell_10 = BankRow.CreateCell(10);

					propcell_10.CellStyle = bodystyle;

					propcell_10.SetCellValue(item.IncomeAdvanceFund);

					var propcell_11 = BankRow.CreateCell(11);

					propcell_11.CellStyle = bodystyle;

					propcell_11.SetCellValue(item.IncomeSecurity);

					var propcell_12 = BankRow.CreateCell(12);

					propcell_12.CellStyle = bodystyle;

					propcell_12.SetCellValue(item.IncomeBankLoan);

					var propcell_13 = BankRow.CreateCell(13);

					propcell_13.CellStyle = bodystyle;

					propcell_13.SetCellValue(item.IncomeProject);

					var propcell_14 = BankRow.CreateCell(14);

					propcell_14.CellStyle = bodystyle;

					propcell_14.SetCellValue(item.IncomeManage);

					var propcell_15 = BankRow.CreateCell(15);

					propcell_15.CellStyle = bodystyle;

					propcell_15.SetCellValue(item.IncomeTax);

					var propcell_16 = BankRow.CreateCell(16);

					propcell_16.CellStyle = bodystyle;

					propcell_16.SetCellValue(item.IncomeOther);

					var propcell_17 = BankRow.CreateCell(17);

					propcell_17.CellStyle = bodystyle;

					propcell_17.SetCellValue(item.Income);

					var propcell_18 = BankRow.CreateCell(18);

					propcell_18.CellStyle = bodystyle;

					propcell_18.SetCellValue(item.CostInnerTransaction);

					var propcell_19 = BankRow.CreateCell(19);

					propcell_19.CellStyle = bodystyle;

					propcell_19.SetCellValue(item.CostRealEstateTransaction);

					var propcell_20 = BankRow.CreateCell(20);

					propcell_20.CellStyle = bodystyle;

					propcell_20.SetCellValue(item.CostAdvanceFund);

					var propcell_21 = BankRow.CreateCell(21);

					propcell_21.CellStyle = bodystyle;

					propcell_21.SetCellValue(item.CostSecurity);

					var propcell_22 = BankRow.CreateCell(22);

					propcell_22.CellStyle = bodystyle;

					propcell_22.SetCellValue(item.CostBankLoan);

					var propcell_23 = BankRow.CreateCell(23);

					propcell_23.CellStyle = bodystyle;

					propcell_23.SetCellValue(item.CostInterest);

					var propcell_24 = BankRow.CreateCell(24);

					propcell_24.CellStyle = bodystyle;

					propcell_24.SetCellValue(item.CostMaterial);

					var propcell_25 = BankRow.CreateCell(25);

					propcell_25.CellStyle = bodystyle;

					propcell_25.SetCellValue(item.CostLabor);

					var propcell_26 = BankRow.CreateCell(26);

					propcell_26.CellStyle = bodystyle;

					propcell_26.SetCellValue(item.CostManage);

					var propcell_27 = BankRow.CreateCell(27);

					propcell_27.CellStyle = bodystyle;

					propcell_27.SetCellValue(item.CostTax);

					var propcell_28 = BankRow.CreateCell(28);

					propcell_28.CellStyle = bodystyle;

					propcell_28.SetCellValue(item.CostReimbursement);

					var propcell_29 = BankRow.CreateCell(29);

					propcell_29.CellStyle = bodystyle;

					propcell_29.SetCellValue(item.CostOther);

					var propcell_30 = BankRow.CreateCell(30);

					propcell_30.CellStyle = bodystyle;

					propcell_30.SetCellValue(item.Cost);

					var propcell_31 = BankRow.CreateCell(31);

					propcell_31.CellStyle = bodystyle;

					propcell_31.SetCellValue(item.EndCash);

					var propcell_32 = BankRow.CreateCell(32);

					propcell_32.CellStyle = bodystyle;

					propcell_32.SetCellValue(item.EndCommercialTicket);

					var propcell_33 = BankRow.CreateCell(33);

					propcell_33.CellStyle = bodystyle;

					propcell_33.SetCellValue(item.EndBalance);

					var propcell_34 = BankRow.CreateCell(34);

					propcell_34.CellStyle = bodystyle;

					propcell_34.SetCellValue(item.EndFreezeMoney);

					var propcell_35 = BankRow.CreateCell(35);

					propcell_35.CellStyle = bodystyle;

					propcell_35.SetCellValue(item.EndFlowBalance);
					#endregion

					#region 相关Project行提取

					projectItem.AddRange(item.children);

					#endregion

					rowNum++;
				}

				#endregion

				#endregion

				#region 项目表数据处理

				var projectsheet = bankworkbook.CreateSheet("项目资金情况" + periodtypeStr + "报表（按银行口径）");

				#region 创建Excel表头
				projectsheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 35));

				IRow projectRowTitle = projectsheet.CreateRow(0);//第一行选中

				rowTitle.Height = 30 * 20;//设置行高

				var projectCellTitle = projectRowTitle.CreateCell(0);

				//给标题头添加格式
				ICellStyle projectMainTitle = bankworkbook.CreateCellStyle();
				projectMainTitle.Alignment = HorizontalAlignment.Center;//单元格水平居中
				projectMainTitle.VerticalAlignment = VerticalAlignment.Center;//单元格垂直居中
				IFont projectMainFontTitle = bankworkbook.CreateFont();//新建一个字体样式对象
				projectMainFontTitle.Boldweight = short.MaxValue;//设置字体加粗样式
				projectMainFontTitle.FontHeightInPoints = 20;//设置字体大小
				projectMainTitle.SetFont(projectMainFontTitle);//使用SetFont方法将字体样式添加到单元格样式中
				projectCellTitle.CellStyle = projectMainTitle;

				projectCellTitle.SetCellValue("财务资金中心银行相关项目收支" + periodtypeStr + "报表");

				//表头明细开始
				ICellStyle projectHeadTitle = bankworkbook.CreateCellStyle();
				projectHeadTitle.Alignment = HorizontalAlignment.Center;//单元格水平居中
				projectHeadTitle.VerticalAlignment = VerticalAlignment.Center;//单元格垂直居中
				projectHeadTitle.FillForegroundColor = HSSFColor.Grey25Percent.Index;  //short.Parse("#E8EAEC");
				IFont projectHeadFontTitle = bankworkbook.CreateFont();//新建一个字体样式对象
				headFontTitle.Boldweight = short.MaxValue;//设置字体加粗样式
				headFontTitle.FontHeightInPoints = 12;//设置字体大小
				projectHeadTitle.SetFont(projectHeadFontTitle);



				IRow projectHeadRowTitle = projectsheet.CreateRow(1);//第二行选中

				#region 第一行，第一列
				projectsheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 3, 0, 0));

				var pOrderTitle = projectHeadRowTitle.CreateCell(0);

				pOrderTitle.CellStyle = headTitle;

				pOrderTitle.SetCellValue("序号");
				#endregion

				#region 第一行，第二列
				projectsheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 3, 1, 1));

				var pAccountTailTitle = projectHeadRowTitle.CreateCell(1);

				pAccountTailTitle.CellStyle = headTitle;

				pAccountTailTitle.SetCellValue("账户尾号");
				#endregion

				#region 第一行，第三列
				projectsheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 3, 2, 2));

				var pBankTitle = projectHeadRowTitle.CreateCell(2);

				pBankTitle.CellStyle = headTitle;

				pBankTitle.SetCellValue("银行名称");
				#endregion

				#region 第一行，第四列
				projectsheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 3, 7));

				var pBeginTitle = projectHeadRowTitle.CreateCell(3);

				pBeginTitle.CellStyle = headTitle;

				pBeginTitle.SetCellValue("期初数");
				#endregion

				#region 第一行，第五列
				projectsheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 8, 17));

				var pIncomeTitle = projectHeadRowTitle.CreateCell(8);

				pIncomeTitle.CellStyle = headTitle;

				pIncomeTitle.SetCellValue("本期收入");
				#endregion

				#region 第一行，第六列
				projectsheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 18, 30));

				var pCostTitle = projectHeadRowTitle.CreateCell(18);

				pCostTitle.CellStyle = headTitle;

				pCostTitle.SetCellValue("本期支出");
				#endregion

				#region 第一行，第七列
				projectsheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 31, 35));

				var pEndTitle = projectHeadRowTitle.CreateCell(31);

				pEndTitle.CellStyle = headTitle;

				pEndTitle.SetCellValue("期末数");
				#endregion

				IRow pBalanceRowTitle = projectsheet.CreateRow(2);//第三行选中

				#region 第二行，第一列
				projectsheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 3, 5));

				var pBeginBalanceTitle = pBalanceRowTitle.CreateCell(3);

				pBeginBalanceTitle.CellStyle = headTitle;

				pBeginBalanceTitle.SetCellValue("期初余额");
				#endregion

				#region 第二行，第二列
				projectsheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 31, 33));

				var pEndBalanceTitle = pBalanceRowTitle.CreateCell(31);

				pEndBalanceTitle.CellStyle = headTitle;

				pEndBalanceTitle.SetCellValue("期末余额");
				#endregion

				#region 第二行，倒数第二列
				projectsheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 3, 34, 34));

				var pEndFreezeMoney = pBalanceRowTitle.CreateCell(34);

				pEndFreezeMoney.CellStyle = headTitle;

				pEndFreezeMoney.SetCellValue("冻结资金");
				#endregion

				#region 第二行，倒数第一列
				projectsheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 3, 35, 35));

				var pEndBalance = pBalanceRowTitle.CreateCell(35);

				pEndBalance.CellStyle = headTitle;

				pEndBalance.SetCellValue("可用余额");
				#endregion

				#region 第二行各列
				String[] pitemArr = new String[]
				{
					"冻结资金","可用余额",
					"内部往来","房产往来","垫资款","保证金","银行借款","工程款", "管理费","税金","其他","收入合计",
					"内部往来","房产往来","垫资款","保证金","银行借款","利息","材料款","人工费","管理费","税金","报销款","其他","支出合计"
				};

				for (int i = 6; i < 31; i++)
				{
					projectsheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 3, i, i));

					var ItemTitle = pBalanceRowTitle.CreateCell(i);

					ItemTitle.CellStyle = headTitle;

					ItemTitle.SetCellValue(pitemArr[i - 6]);
				}
				#endregion

				IRow pBalanceDetailRowTitle = projectsheet.CreateRow(3);//第四行选中

				#region 第四行填充
				String[] pbalanceItem = new String[] { "现金", "商票", "小计" };

				for (int i = 3; i < 6; i++)
				{
					var BeginItemTitle = pBalanceDetailRowTitle.CreateCell(i);

					BeginItemTitle.CellStyle = headTitle;

					BeginItemTitle.SetCellValue(balanceItem[i - 3]);

					var EndItemTitle = pBalanceDetailRowTitle.CreateCell(i + 28);

					EndItemTitle.CellStyle = headTitle;

					EndItemTitle.SetCellValue(pbalanceItem[i - 3]);
				}
				#endregion

				#endregion //表头结尾处

				#region 项目表体处理

				int projectRowNum = 0;

				foreach (var item in projectItem)
				{
                    if (String.IsNullOrEmpty(item.BankName))
                    {
						continue;
                    }

					var ProjectRowNum = projectRowNum + 4;

					IRow BankRow = projectsheet.CreateRow(ProjectRowNum);//第rowNum行数据操作(第五行开始)

					#region Project行赋值
					var propcell_0 = BankRow.CreateCell(0);

					bodystyle.Alignment = HorizontalAlignment.Center;

					propcell_0.CellStyle = bodystyle;

					propcell_0.SetCellValue((projectRowNum + 1).ToString());

					var propcell_1 = BankRow.CreateCell(1);

					propcell_1.CellStyle = bodystyle;

					propcell_1.SetCellValue(item.AccountTail);

					var propcell_2 = BankRow.CreateCell(2);

					propcell_2.CellStyle = bodystyle;

					propcell_2.SetCellValue(item.BankName);

					bodystyle.Alignment = HorizontalAlignment.Left;

					var propcell_3 = BankRow.CreateCell(3);

					propcell_3.CellStyle = bodystyle;

					propcell_3.SetCellValue(item.BeginCash);

					var propcell_4 = BankRow.CreateCell(4);

					propcell_4.CellStyle = bodystyle;

					propcell_4.SetCellValue(item.BeginCommercialTicket);

					var propcell_5 = BankRow.CreateCell(5);

					propcell_5.CellStyle = bodystyle;

					propcell_5.SetCellValue(item.BeginBalance);

					var propcell_6 = BankRow.CreateCell(6);

					propcell_6.CellStyle = bodystyle;

					propcell_6.SetCellValue(item.BeginFreezeMoney);

					var propcell_7 = BankRow.CreateCell(7);

					propcell_7.CellStyle = bodystyle;

					propcell_7.SetCellValue(item.BeginBalance);

					var propcell_8 = BankRow.CreateCell(8);

					propcell_8.CellStyle = bodystyle;

					propcell_8.SetCellValue(item.IncomeInnerTransaction);

					var propcell_9 = BankRow.CreateCell(9);

					propcell_9.CellStyle = bodystyle;

					propcell_9.SetCellValue(item.IncomeRealEstateTransaction);

					var propcell_10 = BankRow.CreateCell(10);

					propcell_10.CellStyle = bodystyle;

					propcell_10.SetCellValue(item.IncomeAdvanceFund);

					var propcell_11 = BankRow.CreateCell(11);

					propcell_11.CellStyle = bodystyle;

					propcell_11.SetCellValue(item.IncomeSecurity);

					var propcell_12 = BankRow.CreateCell(12);

					propcell_12.CellStyle = bodystyle;

					propcell_12.SetCellValue(item.IncomeBankLoan);

					var propcell_13 = BankRow.CreateCell(13);

					propcell_13.CellStyle = bodystyle;

					propcell_13.SetCellValue(item.IncomeProject);

					var propcell_14 = BankRow.CreateCell(14);

					propcell_14.CellStyle = bodystyle;

					propcell_14.SetCellValue(item.IncomeManage);

					var propcell_15 = BankRow.CreateCell(15);

					propcell_15.CellStyle = bodystyle;

					propcell_15.SetCellValue(item.IncomeTax);

					var propcell_16 = BankRow.CreateCell(16);

					propcell_16.CellStyle = bodystyle;

					propcell_16.SetCellValue(item.IncomeOther);

					var propcell_17 = BankRow.CreateCell(17);

					propcell_17.CellStyle = bodystyle;

					propcell_17.SetCellValue(item.Income);

					var propcell_18 = BankRow.CreateCell(18);

					propcell_18.CellStyle = bodystyle;

					propcell_18.SetCellValue(item.CostInnerTransaction);

					var propcell_19 = BankRow.CreateCell(19);

					propcell_19.CellStyle = bodystyle;

					propcell_19.SetCellValue(item.CostRealEstateTransaction);

					var propcell_20 = BankRow.CreateCell(20);

					propcell_20.CellStyle = bodystyle;

					propcell_20.SetCellValue(item.CostAdvanceFund);

					var propcell_21 = BankRow.CreateCell(21);

					propcell_21.CellStyle = bodystyle;

					propcell_21.SetCellValue(item.CostSecurity);

					var propcell_22 = BankRow.CreateCell(22);

					propcell_22.CellStyle = bodystyle;

					propcell_22.SetCellValue(item.CostBankLoan);

					var propcell_23 = BankRow.CreateCell(23);

					propcell_23.CellStyle = bodystyle;

					propcell_23.SetCellValue(item.CostInterest);

					var propcell_24 = BankRow.CreateCell(24);

					propcell_24.CellStyle = bodystyle;

					propcell_24.SetCellValue(item.CostMaterial);

					var propcell_25 = BankRow.CreateCell(25);

					propcell_25.CellStyle = bodystyle;

					propcell_25.SetCellValue(item.CostLabor);

					var propcell_26 = BankRow.CreateCell(26);

					propcell_26.CellStyle = bodystyle;

					propcell_26.SetCellValue(item.CostManage);

					var propcell_27 = BankRow.CreateCell(27);

					propcell_27.CellStyle = bodystyle;

					propcell_27.SetCellValue(item.CostTax);

					var propcell_28 = BankRow.CreateCell(28);

					propcell_28.CellStyle = bodystyle;

					propcell_28.SetCellValue(item.CostReimbursement);

					var propcell_29 = BankRow.CreateCell(29);

					propcell_29.CellStyle = bodystyle;

					propcell_29.SetCellValue(item.CostOther);

					var propcell_30 = BankRow.CreateCell(30);

					propcell_30.CellStyle = bodystyle;

					propcell_30.SetCellValue(item.Cost);

					var propcell_31 = BankRow.CreateCell(31);

					propcell_31.CellStyle = bodystyle;

					propcell_31.SetCellValue(item.EndCash);

					var propcell_32 = BankRow.CreateCell(32);

					propcell_32.CellStyle = bodystyle;

					propcell_32.SetCellValue(item.EndCommercialTicket);

					var propcell_33 = BankRow.CreateCell(33);

					propcell_33.CellStyle = bodystyle;

					propcell_33.SetCellValue(item.EndBalance);

					var propcell_34 = BankRow.CreateCell(34);

					propcell_34.CellStyle = bodystyle;

					propcell_34.SetCellValue(item.EndFreezeMoney);

					var propcell_35 = BankRow.CreateCell(35);

					propcell_35.CellStyle = bodystyle;

					propcell_35.SetCellValue(item.EndFlowBalance);

					#endregion

					projectRowNum++;
				}

				#endregion

				#endregion

				#region 生成Excel文件

				MemoryStream file = new MemoryStream();

				bankworkbook.Write(file, true);

				file.Seek(0, SeekOrigin.Begin);

				return File(file, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "银行口径资金情况(" + periodtypeStr + ")报表.xlsx");
				#endregion
			}
			else //if(BankOrProject == "Project")
            {
                var ProjectReportStr =
                    HttpExtension.HttpGet(baseurl.Value + "/FundsReport/QueryReportByProject?" + querystr, true, "application/json;",AccessToken);

				ProjectReport projectReport = JsonConvert.DeserializeObject<ProjectReport>(ProjectReportStr);

				var projectworkbook = new XSSFWorkbook();

				var projectsheet = projectworkbook.CreateSheet("项目资金情况" + periodtypeStr + "报表");

				#region 创建Excel表头
				projectsheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 27));

				NPOI.SS.UserModel.IRow rowTitle = projectsheet.CreateRow(0);

				rowTitle.Height = 30 * 20;//设置行高

				var CellTitle = rowTitle.CreateCell(0);

                //给标题头添加格式
                ICellStyle styleTitle = projectworkbook.CreateCellStyle();
				styleTitle.Alignment = HorizontalAlignment.Center;//单元格水平居中
				styleTitle.VerticalAlignment = VerticalAlignment.Center;//单元格垂直居中
				IFont fontTitle = projectworkbook.CreateFont();//新建一个字体样式对象
				fontTitle.Boldweight = short.MaxValue;//设置字体加粗样式
				fontTitle.FontHeightInPoints = 16;//设置字体大小
				styleTitle.SetFont(fontTitle);//使用SetFont方法将字体样式添加到单元格样式中
				CellTitle.CellStyle = styleTitle;

				CellTitle.SetCellValue("财务资金中心项目收支" + periodtypeStr + "报表");

				#endregion

				#region 生成Excel文件

				MemoryStream file = new MemoryStream();

				projectworkbook.Write(file,true);

				file.Seek(0, SeekOrigin.Begin);

				return File(file, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "项目资金情况报表.xlsx");
				#endregion

			}
        }
    }
}